![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Statement ParsingOnce the cursor is created, a determination is made about whether the SQL statement is already present in the shared SQL area in the shared pool. If the SQL statement has already been parsed and is in the shared pool, there is no further need for parsing, and the execution of the SQL statement continues. By using stored procedures or by carefully crafting SQL statements to be identical, you stand a good chance that those statements will be in the shared SQL area, already parsed. For an SQL statement to take advantage of SQL or PL/SQL statements that may have already been parsed, the following criteria must be met:
You may think that these conditions make it difficult to take advantage of the shared SQL areas. In fact, users sharing the same application code meet these criteria quite easily. It is to the advantage of the application developer to use the same SQL statements to access the same data, ensuring that SQL statements within the application can also take advantage of the shared SQL areas.
If the statement is not in the shared pool, the following steps are executed to parse the SQL statement:
As you can see from the number of steps that must be executed, it is important to try to keep the SQL statements in the shared pool to avoid the parsing phase of the execution process. Query ProcessingQueries are handled differently than other SQL statements because queries return data as the result of the statement. Other SQL statements need only return a return code that indicates success or failure. In addition to the other steps that must be executed, queries may require the following additional functions:
Only for queries are the preceding functions necessary in addition to the other SQL statement processing. Bind VariablesVariables must be defined for statement to be processed. The program must specify to Oracle the address of the variable before Oracle can bind that variable. Because the binding is done by reference, you do not have to rebind a variable before reexecuting the statement; simply changing its value is sufficient. You must supply the data type and length of each variable you bind to Oracle unless these data types or lengths are implied or defaulted. Statement ExecutionOnce the statement has been parsed and the variables have been defined, the statement is executed. In array processing, the execution step may happen many times. Any necessary locks are applied before the execution of the statement.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |